package rsj.dao;

import com.rlyy.aibase.db.QueryRunner;
import com.rlyy.aibase.utils.base.MapNode;
import com.rlyy.aibase.utils.base.Node;
import com.rlyy.aibase.utils.base.TableNode;
import com.rlyy.aibase.utils.helpers.DateHelper;
import common.util.Assert;
import org.apache.commons.lang3.StringUtils;
import rsj.db.PostgresqlConn;
import rsj.table.*;

import java.sql.Connection;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.util.Date;

public class UserInfoDao {

    private static final UserInfoDao INFO_DAO = new UserInfoDao();

    public static UserInfoDao getInstance() {
        return INFO_DAO;
    }

    public MapNode findUserList(Connection conn, int size, int pcount) throws SQLException {

        QueryRunner queryRunner = new QueryRunner();
        MapNode responsedata = new MapNode();
        StringBuffer countsql = new StringBuffer();
        countsql.append("select count(1) from " + User.Table_Name + " where " + User.User_Type + " = '" + UserType.user_type_sys + "' and " + User.User_Status + " = '" + UserType.user_status_normal + "'");
        Node node = queryRunner.queryLinkNode(conn, countsql.toString());
        int count = node.valueInt();
        responsedata.putNode("User_Count", String.valueOf(count));
        if (count == 0) {
            responsedata.putNode("User_List", "");
            return responsedata;

        }

        StringBuilder sql = new StringBuilder();
        sql.append("select u." + User.User_Id + ",u." + User.User_Name + ",u." + User.User_Uname + ",u." + User.User_Org_Id + ",u." + User.User_Phone)
                .append(",u." + User.User_Wechat + ",r." + Role.Role_Name + ",d." + WebItem.Item_Name + " as Dep_Name")
                .append(" from " + User.Table_Name + " u left join " + Role.Table_Name + " r on u." + User.User_Role_Id + "= r." + Role.Role_Id)
                .append(" left join " + WebItem.Table_Name + " d on u." + User.User_Dep_Id + "=d." + WebItem.Item_Id)
                .append(" where " + User.User_Type + " = '" + UserType.user_type_sys + "' and " + User.User_Status + " = '" + UserType.user_status_normal + "'")
                .append(" order by user_utime desc limit ? Offset ?");

        TableNode map = queryRunner.queryLinkTableNode(conn, sql.toString(), size, size * (pcount - 1));
        responsedata.putNode("User_List", map.toJson());

        return responsedata;

    }


    //public MapNode getByUserUnameAndPassword(Connection conn, String userUname, String password) {
    //    String sql = "SELECT * FROM sys.sys_user WHERE user_uname = '" + userUname + "' AND user_password = '" + password + "'";
    //    return new QueryRunner().queryLinkRow(conn, sql);
    //}
    //
    //public MapNode getByUserUname(Connection conn, String userUname) {
    //    String sql = "SELECT * FROM sys.sys_user WHERE user_uname = '" + userUname + "'";
    //    return new QueryRunner().queryLinkRow(conn, sql);
    //}

    public TableNode checkUserInfo(Connection conn, String useruname) {
        QueryRunner queryRunner = new QueryRunner();
        StringBuffer findsql = new StringBuffer();

        findsql.append("select " + User.User_Password + "," + User.User_Id + " from " + User.Table_Name + " where " + User.User_Uname + " = ? and user_status = ? ");
        TableNode map = queryRunner.queryLinkTableNode(conn, findsql.toString(), useruname, UserType.user_status_normal);

        return map;
    }

    public void insertUser(Connection conn, String id, String userUname, String userName, String orgId, String depId, String userType,
                           String password, String phone, String userWechat, String roleId) {
        QueryRunner queryRunner = new QueryRunner();
        int count = verifyUserByUname(conn,userUname);

        Assert.isTrue(count == 0, "用户名已经存在，不能添加！");

        StringBuffer sql = new StringBuffer();
        sql.append("insert into " + User.Table_Name + " (" + User.User_Id + "," + User.User_Name + "," + User.User_Uname);
        //添加系统用户有部门没有机构Id,添加机构用户，没有部门
        if (StringUtils.isNotBlank(orgId)) {
            sql.append("," + User.User_Org_Id);
            userType = UserType.user_type_org;//若有组织机构 证明该用户为客户
        } else {
            sql.append(",").append(User.User_Dep_Id).append(",").append(User.User_Phone).append(",").append(User.User_Wechat);
        }
        sql.append("," + User.User_Type + "," + User.User_Password)
                .append("," + User.User_Role_Id + "," + User.User_Status + "," + User.User_Ctime + "," + User.User_Utime + "," + User.User_Password_Utime)
                .append(")values(");


        Timestamp timestamp = DateHelper.strToTimestamp(DateHelper.getDate(new Date()));
        if (StringUtils.isNotBlank(orgId)) {
            sql.append("?,?,?,?,?,?,?,?,?,?,?)");
            queryRunner.update(conn, sql.toString(), id, userName, userUname, orgId, userType, password,
                    roleId, UserType.user_status_normal, timestamp, timestamp, timestamp);
        } else {
            sql.append("?,?,?,?,?,?,?,?,?,?,?,?,?)");
            queryRunner.update(conn, sql.toString(), id, userName, userUname, depId, phone, userWechat, userType,
                    password, roleId, UserType.user_status_normal, timestamp, timestamp, timestamp);
        }

    }

    public int verifyUserByUname(Connection conn, String userUname){
        QueryRunner queryRunner = new QueryRunner();
        String findsql = "select count(1) from " + User.Table_Name + " where " + User.User_Uname + " = ? limit 1";
        Node node = queryRunner.queryLinkNode(conn, findsql, userUname);
        return node.valueInt();
    }

    public MapNode findUserByUname(Connection conn, String userUname) throws SQLException {
        QueryRunner queryRunner = new QueryRunner();

        StringBuffer findsql = new StringBuffer();
        findsql.append("select u." + User.User_Id + ",u." + User.User_Name + ",u." + User.User_Uname + ",u." + User.User_Phone)
                .append(",u." + User.User_Wechat + ",d." + WebItem.Item_Name + " as Dep_Name,o." + Org.Org_Id + ",c.cust_name as org_name,u." + User.User_Ctime)
                .append(",u." + User.User_Dep_Id + " as Item_Id ,u." + User.User_Role_Id + " as role_Id");
        findsql.append(" from " + User.Table_Name + " u left join " + WebItem.Table_Name + " d on u." + User.User_Dep_Id + " = d." + WebItem.Item_Id)
                .append(" left join " + Org.Table_Name + " o on u." + User.User_Org_Id + " = o." + Org.Org_Id)
                .append(" left join sys.sys_cust c on c.cust_id = o.org_cust_id ");
        findsql.append(" where u." + User.User_Uname + " = ?");

        MapNode map = queryRunner.queryLinkRow(conn, findsql.toString(), userUname);
        return map;
    }

    /**
     * 修改用户
     *
     * @param conn
     * @param userId
     * @param userUname
     * @param userName
     * @param userPhone
     * @param depId
     * @param roleId
     * @param userPassword
     */
    public void updateUser(Connection conn, String userId, String userUname, String userName, String userPhone,
                           String depId, String userWechat, String roleId, String userPassword) {
        QueryRunner queryRunner = new QueryRunner();
        StringBuilder upsql = new StringBuilder();
        upsql.append("update ").append(User.Table_Name).append(" set ");

        String findsql = "select count(1) from " + User.Table_Name + " where " +
                User.User_Id + " != ? and " + User.User_Uname + " =?";
        Node node = queryRunner.queryLinkNode(conn, findsql, userId, userUname);
        int count = node.valueInt();
        Assert.isTrue(count == 0, "用户名已经存在，不能修改！");
        upsql.append(User.User_Uname).append(" = ? ,");

        upsql.append(User.User_Name).append(" = ? ,");
        upsql.append(User.User_Phone).append(" = ? ,");
        upsql.append(User.User_Dep_Id).append(" = ? ,");
        upsql.append(User.User_Wechat).append(" = ? ,");
        if (StringUtils.isNoneBlank(userPassword)) {
            upsql.append(User.User_Role_Id).append(" = ? ,");
            upsql.append(User.User_Password).append(" = ? ,");
        } else {
            upsql.append(User.User_Role_Id).append(" = ? ,");
        }
        upsql.append(User.User_Utime).append(" = ? ");
        upsql.append("where ").append(User.User_Id).append(" = ? ");

        if (StringUtils.isNoneBlank(userPassword)) {
            queryRunner.update(conn, upsql.toString(), userUname, userName, userPhone, depId, userWechat, roleId, userPassword, DateHelper.strToTimestamp(DateHelper.getDate(new Date())), userId);
        } else {
            queryRunner.update(conn, upsql.toString(), userUname, userName, userPhone, depId, userWechat, roleId, DateHelper.strToTimestamp(DateHelper.getDate(new Date())), userId);
        }

    }

    public void delUser(Connection conn, String userId) {
        QueryRunner queryRunner = new QueryRunner();
        queryRunner.update(conn, "update " + User.Table_Name + " set " + User.User_Status + " = ? ," + User.User_Utime + " = ? where " + User.User_Id + " =?", UserType.user_status_cancel, DateHelper.strToTimestamp(DateHelper.getDate(new Date())), userId);
    }

    public void updatePassword(Connection conn, String userId, String password, String passwordnew) {
        QueryRunner queryRunner = new QueryRunner();

        TableNode map = queryRunner.queryLinkTableNode(conn, "select " + User.User_Password + " from " + User.Table_Name + " where " + User.User_Id + " = ?", userId);
        String ypassword = map.getValues(User.User_Password)[0];

        Assert.isTrue(password.equals(ypassword), "输入的密码不正确，无法修改！");
        queryRunner.update(conn, "update " + User.Table_Name + " set " + User.User_Password + " = ? ," + User.User_Utime + " = ? where " + User.User_Id + " =?", passwordnew, DateHelper.strToTimestamp(DateHelper.getDate(new Date())), userId);
    }

    public MapNode getUserInfoByOrgId(Connection conn, String orgId) {
        String sqlTemp = "select * from " +
                User.Table_Name + " where " + User.User_Org_Id + " = ? ";
        return new QueryRunner().queryLinkRow(conn, sqlTemp, orgId);
    }


    public void updatePassword(Connection conn, String userUname, String passwordnew) {
        QueryRunner queryRunner = new QueryRunner();
        queryRunner.update(conn, "update " + User.Table_Name + " set " + User.User_Password + " = ? ," + User.User_Utime + " = ? where " + User.User_Uname + " =?", passwordnew, DateHelper.strToTimestamp(DateHelper.getDate(new Date())), userUname);
    }

    public void updateUserName(Connection conn, String userUname, String userName) {
        QueryRunner queryRunner = new QueryRunner();
        queryRunner.update(conn, "update " + User.Table_Name + " set " + User.User_Name + " = ? ," + User.User_Utime + " = ? where " + User.User_Uname + " =?", userName, DateHelper.strToTimestamp(DateHelper.getDate(new Date())), userUname);
    }


    public MapNode getUserByUserId(Connection conn, String userId) {
        String sql = "SELECT u.user_id, u.user_uname,u.user_name,u.user_email,r.role_name,user_password_utime FROM sys.sys_user u LEFT JOIN sys.sys_role r ON u.user_role_id = r.role_id WHERE u.user_id = '" + userId + "'";
        return new QueryRunner().queryLinkRow(conn, sql);
    }

    public MapNode getUserByUserName(Connection conn, String userUname) {
        String sqlTemp = "select * from " +
                User.Table_Name + " where " + User.User_Uname + " = ? ";
        return new QueryRunner().queryLinkRow(conn, sqlTemp, userUname);
    }
}
